﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;

public class DBOperations
{
    public static String generateInsertQuery(String tableName, NameValueCollection values)
    {
        String query = String.Empty;
        List<String> systemEntries = new List<String> { "id", "oper" };

        //get list of columns
        query = "INSERT INTO " + tableName + "(";
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i))) continue;
            query += " " + values.GetKey(i) + " ,";
        }

        //get all values
        query = query.Substring(0, query.Length - 1) + " ) VALUES ( ";
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i))) continue;
            query += " '" + values.Get(i) + "' ,";
        }

        //remove last comma
        query = query.Substring(0, query.Length - 1) + " ) ";

        return query;
    }

    public static String generateUpdateQuery(String tableName, NameValueCollection values, List<String> primaryKey)
    {
        String query = String.Empty;
        List<String> systemEntries = new List<String> { "id", "oper" };

        query = "UPDATE " + tableName + " SET ";

        //get statment in format: column_name = 'column_value',...
        for (int i = 0; i < values.Count; i++)
        {
            if (systemEntries.Contains(values.GetKey(i)) || primaryKey.Contains(values.GetKey(i))) { continue; }
            query += values.GetKey(i) + " = '" + values.Get(i) + "' ,";
        }

        //add query part, WHERE
        query = query.Substring(0, query.Length - 1) + " WHERE ";
        foreach (String str in primaryKey)
            query += str + " = '" + values.Get(str) + "' ,";

        //remove last extra comma
        query = query.Substring(0, query.Length - 1);

        return query;
    }

    public static String generateDeleteQuery(String tableName, String key, String value)
    {
        return ( "DELETE FROM " + tableName + " WHERE " + key + " = '" + value + "' " );
    }

}
